
package com.scalar.chiptrack.alarms.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Timestamp;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.Iterator;
import java.util.LinkedHashMap;
import java.util.LinkedList;
import java.util.List;

import com.scalar.ScalarException;
import com.scalar.chiptrack.alarms.Alarm;
import com.scalar.chiptrack.alarms.AlarmUser;
import com.scalar.chiptrack.alarms.Comment;
import com.scalar.chiptrack.db.DBTransaction;
import com.scalar.chiptrack.db.DataSource;
import com.scalar.chiptrack.utils.SqlQueryMappings;


public final class AlarmDAO {


    private AlarmDAO() {
    }
    
    public static void removeAlarmsFromRecipientList (int alarmId, String userId) throws ScalarException {
        Connection conn = null;
        PreparedStatement pstmt = null;
        boolean success = false;

        try {
            conn = DataSource.getConnection();

            pstmt = conn.prepareStatement (SqlQueryMappings.get ("alarms.recipient.removeAlarmsFromRecipientList"));
            pstmt.setInt (1, alarmId);
            pstmt.setString (2, userId);
            
            pstmt.executeUpdate();
            success = true;
        }
        catch (Exception sqle) {
            sqle.printStackTrace();
            throw new ScalarException ("Could not update user response in database", sqle);
        }
        finally {
            try {
                if (conn != null) {
                    if (success == true) {
                        conn.commit();
                    }
                    else {
                        conn.rollback();
                    }
                }
            }
            catch (SQLException ignore) {}

            if (pstmt != null) {
                try { pstmt.close(); } catch (SQLException ignore){}
            }
            if (conn != null) {
                try { conn.close(); } catch (SQLException ignore){}
            }
        }
    }
    
    
    
    public static void updateUserResponse (String userName, int alarmId, String response, Comment comment) throws ScalarException {
        Connection conn = null;
        PreparedStatement pstmt = null;
        boolean success = false;

        try {
            conn = DataSource.getConnection();

            pstmt = conn.prepareStatement (SqlQueryMappings.get ("alarms.recipient.updateRecipient"));
            pstmt.setString (1, response);
            pstmt.setString (2, userName);
            pstmt.setInt (3, alarmId);
            pstmt.executeUpdate();
            pstmt.close();
            pstmt = null;

            if (comment != null) {
                pstmt = conn.prepareStatement(SqlQueryMappings.get ("alarms.comment.insertComment"));
                pstmt.setInt (1, alarmId);
                pstmt.setDate (2, new java.sql.Date (comment.getCreatedOn()));
                pstmt.setString (3, comment.getCreatedBy());
                pstmt.setString (4, comment.getComment());
                pstmt.executeUpdate();
            }

            success = true;
        }
        catch (Exception sqle) {
            sqle.printStackTrace();
            throw new ScalarException ("Could not update user response in database", sqle);
        }
        finally {
            try {
                if (conn != null) {
                    if (success == true) {
                        conn.commit();
                    }
                    else {
                        conn.rollback();
                    }
                }
            }
            catch (SQLException ignore) {}

            if (pstmt != null) {
                try { pstmt.close(); } catch (SQLException ignore){}
            }
            if (conn != null) {
                try { conn.close(); } catch (SQLException ignore){}
            }
        }
    }
    

   
    private static ArrayList buildAlarmsFromResultSet (ResultSet rset, boolean buildAll) throws Exception {
        
        ArrayList alarmList = new ArrayList();
        Alarm alarm = null;
        int alarmId = -1;
        String name = null;
        String type = null;
        String originator = null;
        String originatorEmail = null;
        long createdOn = -1;
        long expiresOn = -1;
        long respondBefore = -1;
        String currentState = null;
        String message = null;
        boolean notifyUpdates = true;
        String priority = null;
        String lastUpdatedBy = null;

        while( rset.next() )
        {
            
            alarmId = rset.getInt ("Alarm_ID");
            type = rset.getString ("Alarm_Type");
            name = rset.getString ("Alarm_Name");
            
            originator = rset.getString ("Created_By");
            
            Timestamp timeStamp = rset.getTimestamp("Created_On");
            createdOn = timeStamp.getTime() + (timeStamp.getNanos()/1000000);
            
            timeStamp = rset.getTimestamp ("Expires_On");
            expiresOn = timeStamp.getTime() + (timeStamp.getNanos()/1000000);
                
            currentState = rset.getString("Current_State");
            message = rset.getString("Message");
            priority = rset.getString ("Priority");
            
            timeStamp = rset.getTimestamp ("Respond_Before");
            if (timeStamp != null) {
                respondBefore = timeStamp.getTime() + (timeStamp.getNanos()/1000000);
            }
            
            lastUpdatedBy = rset.getString ("Last_Updated_By");
            originatorEmail = rset.getString ("Email");

            alarm = new Alarm (name, type, new AlarmUser (originator, originatorEmail), priority, createdOn, expiresOn);
            alarm.setMessage (message);
            alarm.setPriority (priority);
            alarm.setLastUpdatedBy (lastUpdatedBy);
            alarm.setId (alarmId);
            alarm.setCurrentState (currentState);
            alarm.setRespondBefore (respondBefore);

            
            if (buildAll == true) {
                
                // Add recipients
                List recps = loadRecipientsByAlarmId (alarmId);
                alarm.setRecipients (recps);

                // Add comments
                List comments = loadCommentsByAlarmId (null, alarmId);

                int commentsCount = comments.size();
                for (int i=0; i<commentsCount; i++) {
                    alarm.addComment (null/*DBTransaction*/, (Comment)comments.get (i), false/* donot save into database*/);
                }
            }
            
            alarmList.add (alarm);
        }
        
        return alarmList;
    }

    private static HashMap buildAlarmsMapFromResultSet (ResultSet rset, boolean buildAll) throws Exception {
        
        HashMap alarmMap = new HashMap();
        Alarm alarm = null;
        int alarmId = -1;
        String name = null;
        String type = null;
        String originator = null;
        String originatorEmail = null;
        long createdOn = -1;
        long expiresOn = -1;
        long respondBefore = -1;
        String currentState = null;
        String message = null;
        boolean notifyUpdates = true;
        String priority = null;
        String lastUpdatedBy = null;

        while (rset.next()) {
            
            alarmId = rset.getInt ("Alarm_ID");
            type = rset.getString ("Alarm_Type");
            name = rset.getString ("Alarm_Name");
            
            originator = rset.getString ("Created_By");
            
            Timestamp timeStamp = rset.getTimestamp("Created_On");
            createdOn = timeStamp.getTime() + (timeStamp.getNanos()/1000000);
            
            timeStamp = rset.getTimestamp ("Expires_On");
            expiresOn = timeStamp.getTime() + (timeStamp.getNanos()/1000000);
                
            currentState = rset.getString("Current_State");
            message = rset.getString("Message");
            priority = rset.getString ("Priority");
            
            timeStamp = rset.getTimestamp ("Respond_Before");
            if (timeStamp != null) {
                respondBefore = timeStamp.getTime() + (timeStamp.getNanos()/1000000);
            }
            
            lastUpdatedBy = rset.getString ("Last_Updated_By");
            originatorEmail = rset.getString ("Email");

            alarm = new Alarm (name, type, new AlarmUser (originator, originatorEmail), priority, createdOn, expiresOn);
            alarm.setMessage (message);
            alarm.setPriority (priority);
            alarm.setLastUpdatedBy (lastUpdatedBy);
            alarm.setId (alarmId);
            alarm.setCurrentState (currentState);
            alarm.setRespondBefore (respondBefore);

            
            if (buildAll == true) {
                
                // Add recipients
                List recps = loadRecipientsByAlarmId (alarmId);
                alarm.setRecipients (recps);

                // Add comments
                List comments = loadCommentsByAlarmId (null, alarmId);

                int commentsCount = comments.size();
                for (int i=0; i<commentsCount; i++) {
                    alarm.addComment (null/*DBTransaction*/, (Comment)comments.get (i), false/* donot save into database*/);
                }
            }
            
            alarmMap.put (alarmId+"", alarm);
        }
        
        return alarmMap;
    }
    
    
    public static HashMap loadAlarmsByRecipient (String recipientId) throws ScalarException {

        Connection conn = null;
        PreparedStatement pstmt = null;
        HashMap alarmMap = new LinkedHashMap();
        ResultSet rset = null;

        if (recipientId == null) {
            return alarmMap;
        }
        
        try {
            conn = DataSource.getConnection();

            pstmt = conn.prepareStatement (SqlQueryMappings.get ("alarms.selectAlarmsByRecipient"));
            pstmt.setString (1, recipientId);
            rset = pstmt.executeQuery();
            
            ArrayList alarmList = null;
            Alarm alarm = null;
            int alarmId = -1;
            String name = null;
            String type = null;
            String originator = null;
            String originatorEmail = null;
            long createdOn = -1;
            long expiresOn = -1;
            long respondBefore = -1;
            String currentState = null;
            String message = null;
            boolean notifyUpdates = true;
            String priority = null;
            String lastUpdatedBy = null;

            while (rset.next()) {
                //Alarm.Alarm_ID, Alarm_Type, Alarm_Name, Created_By, Created_On, Expires_On, Current_State, Message, Priority, Last_Updated_By, User_Table.Email 
                alarmId = rset.getInt ("Alarm_ID");
                type = rset.getString ("Alarm_Type");                
                name = rset.getString ("Alarm_Name");
                
                originator = rset.getString ("Created_By");

                Timestamp timeStamp = rset.getTimestamp("Created_On");
                createdOn = timeStamp.getTime() + (timeStamp.getNanos()/1000000);

                timeStamp = rset.getTimestamp ("Expires_On");
                expiresOn = timeStamp.getTime() + (timeStamp.getNanos()/1000000);

                currentState = rset.getString("Current_State");
                message = rset.getString("Message");
                priority = rset.getString ("Priority");
                
                timeStamp = rset.getTimestamp ("Respond_Before");
                if (timeStamp != null) {
                    respondBefore = timeStamp.getTime() + (timeStamp.getNanos()/1000000);
                }
                
                lastUpdatedBy = rset.getString ("Last_Updated_By");
                originatorEmail = rset.getString ("Email");

                alarm = new Alarm (name, type, new AlarmUser (originator, originatorEmail), priority, createdOn, expiresOn);
                alarm.setMessage (message);
                alarm.setPriority (priority);
                alarm.setLastUpdatedBy (lastUpdatedBy);
                alarm.setId (alarmId);
                alarm.setCurrentState (currentState);
                alarm.setRespondBefore (respondBefore);

                alarmList = (ArrayList) alarmMap.get (alarm.getType());
                if (alarmList == null) {
                    alarmList = new ArrayList();
                }
                
                alarmList.add (alarm);
                alarmMap.put (alarm.getType(), alarmList);
            }
        }
        catch (Exception sqle) {
            sqle.printStackTrace();
            throw new ScalarException ("Could not load Alarm List from database for the recipient: " + recipientId, sqle);
        }
        finally {
            if (rset != null) {
                try { rset.close(); } catch (SQLException ignore){}
            }
            if (pstmt != null) {
                try { pstmt.close(); } catch (SQLException ignore){}
            }
            if (conn != null) {
                try { conn.close(); } catch (SQLException ignore){}
            }
        }

        return alarmMap;
    }
    
    
    public static ArrayList loadOpenAlarms() throws ScalarException {

        Connection conn = null;
        PreparedStatement pstmt = null;
        ArrayList alarmList = null;
        ResultSet rset = null;

        try {
            conn = DataSource.getConnection();

            pstmt = conn.prepareStatement (SqlQueryMappings.get ("alarms.selectOpenAlarms"));
            pstmt.setString (1, Alarm.ALARM_STATUS_CLOSED);
            rset = pstmt.executeQuery();

            alarmList = buildAlarmsFromResultSet (rset, false /* dont build recipient and comment list */);
        }
        catch (Exception sqle) {
            sqle.printStackTrace();
            throw new ScalarException ("Could not load Alarm from database", sqle);
        }
        finally {
            if (rset != null) {
                try { rset.close(); } catch (SQLException ignore){}
            }
            if (pstmt != null) {
                try { pstmt.close(); } catch (SQLException ignore){}
            }
            if (conn != null) {
                try { conn.close(); } catch (SQLException ignore){}
            }
        }

        return alarmList;
    }
    
    
    public static HashMap loadAlarmByIdList (Iterator alarmIdIterator) throws ScalarException {
        
        Connection conn = null;
        PreparedStatement pstmt = null;
        HashMap alarmMap = new HashMap();
        ResultSet rset = null;

        try {
            conn = DataSource.getConnection();

            StringBuffer query = new StringBuffer();
            query.append (SqlQueryMappings.get ("alarms.selectAlarmsByIdList")).append (" (");
            while (alarmIdIterator.hasNext()) {
                query.append (alarmIdIterator.next());
                
                if (alarmIdIterator.hasNext() == true) {
                    query.append (",");
                }
            }
            query.append (")");
            
            pstmt = conn.prepareStatement (query.toString());
            rset = pstmt.executeQuery();
            
            alarmMap = buildAlarmsMapFromResultSet (rset, false /* dont build recipient and comment list */);
        }
        catch (Exception sqle) {
            sqle.printStackTrace();
            throw new ScalarException ("Could not load Alarm by alarm ID list from database", sqle);
        }
        finally {
            if (rset != null) {
                try { rset.close(); } catch (SQLException ignore){}
            }
            if (pstmt != null) {
                try { pstmt.close(); } catch (SQLException ignore){}
            }
            if (conn != null) {
                try { conn.close(); } catch (SQLException ignore){}
            }
        }

        return alarmMap;
    }
    
    
    public static Alarm loadAlarmById (int alarmId) throws ScalarException {
        
        Connection conn = null;
        PreparedStatement pstmt = null;
        Alarm alarm = null;
        ResultSet rset = null;

        try {
            conn = DataSource.getConnection();

            pstmt = conn.prepareStatement (SqlQueryMappings.get ("alarms.selectAlarmsById"));
            pstmt.setInt (1, alarmId);

            rset = pstmt.executeQuery();
            ArrayList alarmList = buildAlarmsFromResultSet (rset, true);  // just to resue buildAlarmsFromResultSet() method -- 
                                                                             // Doing this stuff
            if ((alarmList != null) && (alarmList.size() > 0)) {            // ideally the size should be 1 always
                alarm = (Alarm) alarmList.get(0);
            }
            
        }
        catch (Exception sqle) {
            sqle.printStackTrace();
            throw new ScalarException ("Could not load Alarm from database", sqle);
        }
        finally {
            if (rset != null) {
                try { rset.close(); } catch (SQLException ignore){}
            }
            if (pstmt != null) {
                try { pstmt.close(); } catch (SQLException ignore){}
            }
            if (conn != null) {
                try { conn.close(); } catch (SQLException ignore){}
            }
        }

        return alarm;
    }

    public static Alarm loadQuotationAlarmById ( DBTransaction dbTransaction, int alarmId ) throws ScalarException
    {

        Connection conn         = null;
        PreparedStatement pstmt = null;
        ResultSet rset          = null;
        Alarm alarm             = null;

        long createdOn          = -1;
        long expiresOn          = -1;
        long respondBefore      = -1;
        int commentsCount       = 0;

        String name             = null;
        String type             = null;
        String originator       = null;
        String originatorEmail  = null;
        String currentState     = null;
        String message          = null;
        String priority         = null;
        String lastUpdatedBy    = null;
        Timestamp timeStamp     = null;
        List comments           = null;

        try
        {
            if( dbTransaction == null )
            {
                conn = DataSource.getConnection();
            }
            else
            {
                conn = dbTransaction.getConnection();
            }

            pstmt = conn.prepareStatement( SqlQueryMappings.get( "alarms.selectAlarmsById" ) );
            pstmt.setInt ( 1, alarmId );
            rset = pstmt.executeQuery();

            if( rset.next() )
            {

                alarmId     = rset.getInt ( "Alarm_ID" );
                type        = rset.getString ( "Alarm_Type" );
                name        = rset.getString ( "Alarm_Name" );
                originator  = rset.getString ( "Created_By" );
                timeStamp   = rset.getTimestamp( "Created_On" );
                createdOn   = timeStamp.getTime() + ( timeStamp.getNanos() / 1000000 );
                timeStamp   = rset.getTimestamp ( "Expires_On" );
                expiresOn   = timeStamp.getTime() + ( timeStamp.getNanos() / 1000000 );
                currentState= rset.getString( "Current_State" );
                message     = rset.getString( "Message" );
                priority    = rset.getString ( "Priority" );
                timeStamp   = rset.getTimestamp ( "Respond_Before" );
                if ( timeStamp != null )
                {
                    respondBefore = timeStamp.getTime() + ( timeStamp.getNanos() / 1000000 );
                }
                lastUpdatedBy   = rset.getString ( "Last_Updated_By" );
                originatorEmail = rset.getString ( "Email");

                alarm = new Alarm( name, type, new AlarmUser ( originator, originatorEmail ), priority, createdOn, expiresOn );
                alarm.setMessage( message );
                alarm.setPriority( priority );
                alarm.setLastUpdatedBy( lastUpdatedBy );
                alarm.setId( alarmId );
                alarm.setCurrentState( currentState );
                alarm.setRespondBefore( respondBefore );


            }
        }
        catch (Exception sqle)
        {
            sqle.printStackTrace();
            throw new ScalarException ("Could not load Alarm from database", sqle);
        }
        finally
        {
            if (rset != null) {
                try { rset.close(); } catch (SQLException ignore){}
            }
            if (pstmt != null) {
                try { pstmt.close(); } catch (SQLException ignore){}
            }
            if (conn != null) {
                try { conn.close(); } catch (SQLException ignore){}
            }
        }

        // Load Recipients and Comments
        if( alarm != null )
        {
            alarm.setRecipients( loadQuotationRecipientsByAlarmId ( dbTransaction, alarmId ) );

            comments = loadCommentsByAlarmId( dbTransaction, alarmId );
            commentsCount = comments.size();

            for ( int i = 0; i < commentsCount; i++ )
            {
                alarm.addComment( dbTransaction, ( Comment ) comments.get( i ), false /* donot save into database*/ );
            }
        }

        return alarm;
    }


    public static Alarm loadSalesOrderAlarmById ( DBTransaction dbTransaction, int alarmId ) throws ScalarException
    {

        Connection conn         = null;
        PreparedStatement pstmt = null;
        ResultSet rset          = null;
        Alarm alarm             = null;

        long createdOn          = -1;
        long expiresOn          = -1;
        long respondBefore      = -1;
        int commentsCount       = 0;

        String name             = null;
        String type             = null;
        String originator       = null;
        String originatorEmail  = null;        
        String currentState     = null;
        String message          = null;
        String priority         = null;
        String lastUpdatedBy    = null;
        Timestamp timeStamp     = null;
        List comments           = null;

        try
        {
            if( dbTransaction == null )
            {
                conn = DataSource.getConnection();
            }
            else
            {
                conn = dbTransaction.getConnection();
            }

            pstmt = conn.prepareStatement( SqlQueryMappings.get( "alarms.selectAlarmsById" ) );
            pstmt.setInt ( 1, alarmId );
            rset = pstmt.executeQuery();

            if( rset.next() )
            {

                alarmId     = rset.getInt ( "Alarm_ID" );
                type        = rset.getString ( "Alarm_Type" );
                name        = rset.getString ( "Alarm_Name" );
                originator  = rset.getString ( "Created_By" );
                timeStamp   = rset.getTimestamp( "Created_On" );
                createdOn   = timeStamp.getTime() + ( timeStamp.getNanos() / 1000000 );
                timeStamp   = rset.getTimestamp ( "Expires_On" );
                expiresOn   = timeStamp.getTime() + ( timeStamp.getNanos() / 1000000 );
                currentState= rset.getString( "Current_State" );
                message     = rset.getString( "Message" );
                priority    = rset.getString ( "Priority" );
                timeStamp   = rset.getTimestamp ( "Respond_Before" );
                if ( timeStamp != null )
                {
                    respondBefore = timeStamp.getTime() + ( timeStamp.getNanos() / 1000000 );
                }
                lastUpdatedBy   = rset.getString ( "Last_Updated_By" );
                originatorEmail = rset.getString ( "Email");

                alarm = new Alarm( name, type, new AlarmUser ( originator, originatorEmail ), priority, createdOn, expiresOn );
                alarm.setMessage( message );
                alarm.setPriority( priority );
                alarm.setLastUpdatedBy( lastUpdatedBy );
                alarm.setId( alarmId );
                alarm.setCurrentState( currentState );
                alarm.setRespondBefore( respondBefore );


            }
        }
        catch (Exception sqle)
        {
            sqle.printStackTrace();
            throw new ScalarException ("Could not load Alarm from database", sqle);
        }
        finally
        {
            if (rset != null) {
                try { rset.close(); } catch (SQLException ignore){}
            }
            if (pstmt != null) {
                try { pstmt.close(); } catch (SQLException ignore){}
            }
            if (conn != null) {
                try { conn.close(); } catch (SQLException ignore){}
            }
        }

        // Load Recipients and Comments
        if( alarm != null )
        {
            alarm.setRecipients( loadSalesOrderRecipientsByAlarmId ( dbTransaction, alarmId ) );

            comments = loadCommentsByAlarmId( dbTransaction, alarmId );
            commentsCount = comments.size();

            for ( int i = 0; i < commentsCount; i++ )
            {
                alarm.addComment( dbTransaction, ( Comment ) comments.get( i ), false /* donot save into database*/ );
            }
        }

        return alarm;
    }


    
    private static List loadRecipientsByAlarmId (int alarmId) throws ScalarException {
        
        Connection conn = null;
        PreparedStatement pstmt = null;
        ArrayList recpList = new ArrayList();
        ResultSet rset = null;

        try {
            conn = DataSource.getConnection();

            pstmt = conn.prepareStatement (SqlQueryMappings.get ("alarms.recipient.selectRecipientsByAlarmId"));
            pstmt.setInt (1, alarmId);
            rset = pstmt.executeQuery();

            
            while (rset.next()) {
                
                recpList.add (new AlarmUser (rset.getString ("User_ID"),
                                              rset.getString ("Email"),
                                              rset.getString ("Alarm_Originator"),
                                              rset.getString ("Response"),
                                              rset.getInt ("AU_level")));
            }
        }
        catch (Exception sqle) {
            sqle.printStackTrace();
            throw new ScalarException ("Could not load recipients from database", sqle);
        }
        finally {
            if (rset != null) {
                try { rset.close(); } catch (SQLException ignore){}
            }
            if (pstmt != null) {
                try { pstmt.close(); } catch (SQLException ignore){}
            }
            if (conn != null) {
                try { conn.close(); } catch (SQLException ignore){}
            }
        }
        return recpList;
    }

    private static List loadQuotationRecipientsByAlarmId( DBTransaction dbTransaction, int alarmId ) throws ScalarException
    {

        Connection conn         = null;
        PreparedStatement pstmt = null;
        ArrayList recpList      = new ArrayList();
        ResultSet rset          = null;

        try
        {
            if( dbTransaction == null )
            {
                conn = DataSource.getConnection();
            }
            else
            {
                conn = dbTransaction.getConnection();
            }

            pstmt = conn.prepareStatement ( SqlQueryMappings.get ( "alarms.recipient.selectQuotationRecipientsByAlarmId" ) );
            pstmt.setInt (1, alarmId);
            rset = pstmt.executeQuery();


            while ( rset.next() )
            {

                recpList.add ( new AlarmUser (  rset.getString ("User_ID"),
                                                rset.getString ("Email"),
                                                rset.getString ("Alarm_Originator"),
                                                rset.getString ("Response"),
                                                rset.getInt ("AU_level") ) );
            }
        }
        catch (Exception sqle)
        {
            sqle.printStackTrace();
            throw new ScalarException ("Could not load SalesOrder recipients from database", sqle);
        }
        finally
        {
            if (rset != null) {
                try { rset.close(); } catch (SQLException ignore){}
            }
            if (pstmt != null) {
                try { pstmt.close(); } catch (SQLException ignore){}
            }
            if (conn != null) {
                try { conn.close(); } catch (SQLException ignore){}
            }
        }

        return recpList;
    }

    private static List loadSalesOrderRecipientsByAlarmId( DBTransaction dbTransaction, int alarmId ) throws ScalarException
    {

        Connection conn         = null;
        PreparedStatement pstmt = null;
        ArrayList recpList      = new ArrayList();
        ResultSet rset          = null;

        try
        {
            if( dbTransaction == null )
            {
                conn = DataSource.getConnection();
            }
            else
            {
                conn = dbTransaction.getConnection();
            }

            pstmt = conn.prepareStatement ( SqlQueryMappings.get ( "alarms.recipient.selectSalesOrderRecipientsByAlarmId" ) );
            pstmt.setInt (1, alarmId);
            rset = pstmt.executeQuery();


            while ( rset.next() )
            {

                recpList.add ( new AlarmUser (  rset.getString ("User_ID"),
                                                rset.getString ("Email"),
                                                rset.getString ("Alarm_Originator"),
                                                rset.getString ("Response"),
                                                rset.getInt ("AU_level") ) );
            }
        }
        catch (Exception sqle)
        {
            sqle.printStackTrace();
            throw new ScalarException ("Could not load SalesOrder recipients from database", sqle);
        }
        finally
        {
            if (rset != null) {
                try { rset.close(); } catch (SQLException ignore){}
            }
            if (pstmt != null) {
                try { pstmt.close(); } catch (SQLException ignore){}
            }
            if (conn != null) {
                try { conn.close(); } catch (SQLException ignore){}
            }
        }

        return recpList;
    }


    
    public static LinkedList loadToBeRespondedRecipients() throws ScalarException {
        
        Connection conn = null;
        PreparedStatement pstmt = null;
        LinkedList recpList = new LinkedList();
        ResultSet rset = null;

        try {
            conn = DataSource.getConnection();

            pstmt = conn.prepareStatement (SqlQueryMappings.get ("alarms.recipient.loadToBeRespondedRecipients"));
            pstmt.setString (1, Alarm.ALARM_RESPONSE_NOT_RESPONDED_YET);
            rset = pstmt.executeQuery();

            while (rset.next()) {

                recpList.addLast (new AlarmUser (rset.getString ("User_ID"),
                                              rset.getString ("Email"),
                                              rset.getString ("Created_By"),
                                              rset.getString ("Response"),
                                              rset.getInt ("AU_Level"),
                                              rset.getInt ("Alarm_ID")));
            }
        }
        
        catch (Exception sqle) {
            sqle.printStackTrace();
            throw new ScalarException ("Could not load recipients from database", sqle);
        }
        
        finally {
            if (rset != null) {
                try { rset.close(); } catch (SQLException ignore){}
            }
            if (pstmt != null) {
                try { pstmt.close(); } catch (SQLException ignore){}
            }
            if (conn != null) {
                try { conn.close(); } catch (SQLException ignore){}
            }
        }
        
        return recpList;
    }
    
    
    
    public static ArrayList loadCommentsByAlarmId( DBTransaction dbTransaction, int alarmId ) throws ScalarException
    {
        
        Connection conn = null;
        PreparedStatement pstmt = null;
        ArrayList commentList = new ArrayList();
        ResultSet rset = null;
        
        try
        {
            if( dbTransaction == null )
            {
                conn = DataSource.getConnection();
            }
            else
            {
                conn = dbTransaction.getConnection();
            }

            pstmt = conn.prepareStatement (SqlQueryMappings.get ("alarms.comment.selectCommentsByAlarmId"));
            pstmt.setInt (1, alarmId);

            rset = pstmt.executeQuery();

            long createdOn = -1;
            String createdBy = null;
            String comments = null;
            Timestamp timeStamp = null;
            
            while (rset.next()) {
            
                timeStamp = rset.getTimestamp("Created_On");
                createdOn = timeStamp.getTime() + (timeStamp.getNanos()/1000000);
                
                createdBy = rset.getString("Created_By");
                comments = rset.getString("Comments");
            
                commentList.add (new Comment (createdOn, createdBy, comments));
            }
        }
        catch (Exception sqle) {
            sqle.printStackTrace();
            throw new ScalarException ("Could not load comments from database", sqle);
        }
        finally {
            if (rset != null) {
                try { rset.close(); } catch (SQLException ignore){}
            }
            if (pstmt != null) {
                try { pstmt.close(); } catch (SQLException ignore){}
            }
            if (conn != null) {
                try { conn.close(); } catch (SQLException ignore){}
            }
        }
        return commentList;
    }

    
    public static void insertComment (DBTransaction dbTransaction, int alarmId, Comment comment, String state, String updatedBy) throws ScalarException {

        Connection conn = null;
        PreparedStatement pstmt = null;
        boolean success = false;

        try {
            // Get the connection from regular datasource if its not in a manual transaction
            if (dbTransaction == null) {
                conn = DataSource.getConnection();
            }
            else {
                conn = dbTransaction.getConnection();
            }

            // INSERT COMMENT
            pstmt = conn.prepareStatement (SqlQueryMappings.get ("alarms.comment.insertComment"));
            pstmt.setInt (1, alarmId);
            pstmt.setDate (2, new java.sql.Date (comment.getCreatedOn()));
            pstmt.setString (3, comment.getCreatedBy());
            pstmt.setString (4, comment.getComment());
            pstmt.execute();

            pstmt.close();
            pstmt = null;
            
            // UPDATE ALARM
            pstmt = conn.prepareStatement (SqlQueryMappings.get ("alarms.updateAlarmState"));
            pstmt.setString (1, state);
            pstmt.setString (2, updatedBy);
            pstmt.setInt (3, alarmId);
            pstmt.executeUpdate();
            
            success = true;
        }
        
        catch (Exception sqle) {
            sqle.printStackTrace();
            throw new ScalarException ("Could not store Alarm into database", sqle);
        }
        
        finally {
            // Commit/Rollback here it selef -- if its not in a manual transaction
            if (dbTransaction == null) {
                try {
                    if (conn != null) {
                        if (success == true) {
                            conn.commit();
                        }
                        else {
                            conn.rollback();
                        }
                    }
                }
                catch (SQLException ignore) {}
            }

            if (pstmt != null) {
                try { pstmt.close(); } catch (SQLException ignore){}
            }
            if (conn != null) {
                try { conn.close(); } catch (SQLException ignore){}
            }
        }
    }

    
    /**
     * 1. Updates alarm table -> alarm_state
     * 2. Updates recipient table -> response
     * 3. Inserts comment table -> comment
     */
    public static void insertUserResponse (String userName, int alarmId, String response, String comment, String currentState, String updatedBy) throws ScalarException {
        Connection conn = null;
        PreparedStatement pstmt = null;
        boolean success = false;

        try {
            conn = DataSource.getConnection();

            // UPDATE RECIPIENT TABLE
            pstmt = conn.prepareStatement (SqlQueryMappings.get ("alarms.recipient.updateRecipient"));
            pstmt.setString (1, response);
            pstmt.setString (2, userName);
            pstmt.setInt (3, alarmId);            
            pstmt.execute();
            pstmt.close();
            pstmt = null;

            // INSERT COMMENT
            pstmt = conn.prepareStatement (SqlQueryMappings.get ("alarms.comment.insertComment"));
            pstmt.setInt (1, alarmId);
            pstmt.setDate   (2, new java.sql.Date (System.currentTimeMillis()));
            pstmt.setString (3, userName);
            pstmt.setString (4, comment);
            pstmt.execute();
            pstmt.close();
            pstmt = null;
            
            // UPDATE ALARM STATE
            pstmt = conn.prepareStatement (SqlQueryMappings.get ("alarms.updateAlarmState"));
            pstmt.setString (1, currentState);
            pstmt.setString (2, updatedBy);
            pstmt.setInt (3, alarmId);
            pstmt.execute();
            pstmt.close();
            pstmt = null;
            
            success = true;
        }
        catch (Exception sqle) {
            sqle.printStackTrace();
            throw new ScalarException ("Could not store Alarm into database", sqle);
        }
        finally {
            try {
                if (conn != null) {
                    if (success == true) {
                        conn.commit();
                    }
                    else {
                        conn.rollback();
                    }
                }
            }
            catch (SQLException ignore) {}

            if (pstmt != null) {
                try { pstmt.close(); } catch (SQLException ignore){}
            }
            if (conn != null) {
                try { conn.close(); } catch (SQLException ignore){}
            }
        }
    }

    
    public static void updateAlarmState (int alarmId, String state, String updatedBy) throws ScalarException {
        
        Connection conn = null;
        PreparedStatement pstmt = null;
        boolean success = false;

        try {
            conn = DataSource.getConnection();

            // UPDATE ALARM
            pstmt = conn.prepareStatement (SqlQueryMappings.get ("alarms.updateAlarmState"));
            pstmt.setString (1, state);
            pstmt.setString (2, updatedBy);
            pstmt.setInt (3, alarmId);

            pstmt.executeUpdate();
            success = true;
        }
        catch (Exception sqle) {
            sqle.printStackTrace();
            throw new ScalarException ("Could not update Alarm state to \"" + state + "\"", sqle);
        }
        finally {
            try {
                if (conn != null) {
                    if (success == true) {
                        conn.commit();
                    }
                    else {
                        conn.rollback();
                    }
                }
            }
            catch (SQLException ignore) {}

            if (pstmt != null) {
                try { pstmt.close(); } catch (SQLException ignore){}
            }
            if (conn != null) {
                try { conn.close(); } catch (SQLException ignore){}
            }
        }
    }

    public static void insertAlarm (DBTransaction dbTransaction, Alarm alarm) throws ScalarException
    {

        Connection conn         = null;
        PreparedStatement pstmt = null;
        ResultSet rset          = null;
        boolean success         = false;

        try
        {
            if ( dbTransaction == null )
            {
                conn = DataSource.getConnection();
            }
            else
            {
                conn = dbTransaction.getConnection();
            }

            // STORE ALARM
            pstmt = conn.prepareStatement( SqlQueryMappings.get ( "alarms.insertAlarm" ) );
            
            pstmt.setString ( 1, alarm.getName() );
            pstmt.setString ( 2, alarm.getType() );
            pstmt.setString ( 3, alarm.getOriginator().getName() );
            pstmt.setDate   ( 4, new java.sql.Date( alarm.getCreatedOn() ) );
            pstmt.setDate   ( 5, new java.sql.Date( alarm.getExpiresOn() ) );
            pstmt.setString ( 6, alarm.getCurrentState() );
            pstmt.setString ( 7, alarm.getMessage() );
            pstmt.setString ( 8, alarm.getPriority() );
            pstmt.setDate   ( 9, new java.sql.Date( alarm.getRespondBefore() ) );
            pstmt.setString ( 10, alarm.getLastUpdatedBy() );

            pstmt.execute();

            pstmt.close();
            pstmt = null;

            // GET ALARM ID
            pstmt = conn.prepareStatement( "SELECT @@IDENTITY as alarm_id" );
            rset = pstmt.executeQuery();
            int alarmId = -1;
            
            if ( rset.next() )
            {
                alarmId =  rset.getInt( "alarm_id" );
            }

            alarm.setId ( alarmId );
            
            rset.close();
            rset = null;
            
            pstmt.close();
            pstmt = null;

            // STORE RECIPIENTS
            pstmt = conn.prepareStatement( SqlQueryMappings.get( "alarms.recipient.insertRecipient" ) );

            Iterator users = alarm.getRecipients();
            AlarmUser user = null;

            while( users.hasNext() )
            {
                user = ( AlarmUser ) users.next();

                pstmt.setString ( 1, user.getName() );
                pstmt.setInt    ( 2, alarmId );
                pstmt.setString ( 3, alarm.getOriginator().getName() );
                pstmt.setString ( 4, user.getResponse() );
                pstmt.setInt    ( 5, user.getLevel() );

                pstmt.addBatch();
            }
            
            pstmt.executeBatch();

            pstmt.close();
            pstmt = null;

            // STORE COMMETNS
            pstmt = conn.prepareStatement( SqlQueryMappings.get( "alarms.comment.insertComment" ) );

            List comments   = alarm.getComments();
            int size        = comments.size();
            Comment comment = null;

            for( int i = 0; i < size; i++ ) 
            {
                comment = ( Comment ) comments.get( i );

                pstmt.setInt    ( 1, alarmId );
                pstmt.setDate   ( 2, new java.sql.Date( comment.getCreatedOn() ) );
                pstmt.setString ( 3, comment.getCreatedBy() );
                pstmt.setString ( 4, comment.getComment() );

                pstmt.addBatch();
            }
            
            pstmt.executeBatch();

            pstmt.close();
            pstmt = null;
            
            success = true;
        }
        catch( Exception sqle )
        {
            sqle.printStackTrace();
            throw new ScalarException ( "Could not store Alarm into database", sqle );
        }
        finally
        {
            if( dbTransaction == null ) 
            {
                try {
                    if (conn != null) {
                        if (success == true) {
                            conn.commit();
                        }
                        else {
                            conn.rollback();
                        }
                    }
                }
                catch (SQLException ignore) {}
            }

            if (rset != null) {
                try { rset.close(); } catch (SQLException ignore){}
            }
            if (pstmt != null) {
                try { pstmt.close(); } catch (SQLException ignore){}
            }
            if (conn != null) {
                try { conn.close(); } catch (SQLException ignore){}
            }
        }
    }

    // Added by Prabhanjan on 31st Dec 2003 for sending mails to recipients based on Board or Kit or Chip Modify Access
    // Based on line items (Board, Kit and Chip) present in the sales order, mails are sent to the respective recipients
    public static ArrayList loadRecipientsWithBoardKitChipAccess ( int salesOrderId, Alarm alarm ) throws ScalarException
    {
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rset = null;

        ArrayList recipientList = new ArrayList ();
        ArrayList users = new ArrayList ();
        ArrayList pTypeList = new ArrayList ();
        String functionNames = "";

        try
        {
            conn = DataSource.getConnection ();
            pstmt = conn.prepareStatement ( SqlQueryMappings.get ( "alarm.loadProductTypeForLineItem" ) );
            pstmt.setInt ( 1, salesOrderId );
            pstmt.setInt ( 2, salesOrderId );
            rset = pstmt.executeQuery ();

            while ( rset.next () )
            {
                String pType = rset.getString ( "p_type" );

                if ( pType == null )
                {
                    pType = "Chip Notifications";
                }
                else if ( pType.equals ( "BOARD" ) )
                {
                    pType = "Board Notifications";
                }
                else if ( pType.equals ( "KIT" ) )
                {
                    pType = "Kit Notifications";
                }
                else if ( pType.equals ( "software" ) )   // functionality table has got no corresponsing entry related to s/w at present changed by murali krishna yengala
                {
                    pType = "Board Notifications";
                }
                pTypeList.add ( pType );
            }

            if ( pTypeList.indexOf ( "Chip Notifications" ) != -1 )
            {
                functionNames = "'Chip Notifications'";
            }

            if ( pTypeList.indexOf ( "Board Notifications" ) != -1 )
            {
                if ( functionNames.equals ( "" ) )
                {
                    functionNames = "'Board Notifications'";
                }
                else
                {
                    functionNames = functionNames + ", 'Board Notifications'";
                }
            }

            if ( pTypeList.indexOf ( "Kit Notifications" ) != -1 )
            {
                if ( functionNames.equals ( "" ) )
                {
                    functionNames = "'Kit Notifications'";
                }
                else
                {
                    functionNames = functionNames + ", 'Kit Notifications'";
                }
            }

            // Added by Prabhanjan on (17 Jun 2004) to avoid SQLException
            if("".equals(functionNames))
            {
                functionNames = "'0'";
            }

            StringBuffer query = new StringBuffer ();
            query.append ( SqlQueryMappings.get ( "alarms.recipient.loadRecipientsWithBoardKitChipAccess" ) ).append ( " (" );
            query.append ( functionNames );
            query.append ( ") " );
            query.append ( "AND USER_ID IN ( " );

            Iterator recipients = alarm.getRecipients ();

            // Added by Prabhanjan on (17 Jun 2004) to avoid SQLException
            if(! ( recipients.hasNext() ) )
            {
                query.append("'0'");
            }
            while ( recipients.hasNext () )
            {
                AlarmUser alarmUser = ( AlarmUser ) recipients.next ();
                query.append ( "'" + alarmUser.getName () + "'" );
                if ( recipients.hasNext () )
                {
                    query.append ( ", " );
                }

            }

            query.append ( " )" );
            pstmt = conn.prepareStatement ( query.toString () );
            //System.out.println(query);
            rset = pstmt.executeQuery ();

            while ( rset.next () )
            {
                String userId = rset.getString ( "user_id" );
                users.add ( userId );
            }

            recipients = alarm.getRecipients ();
            for ( int i = 0; i < users.size (); i++ )
            {
                String userId = ( String ) users.get ( i );
                while ( recipients.hasNext () )
                {
                    AlarmUser alarmUser = ( AlarmUser ) recipients.next ();

                    if ( userId.equals ( alarmUser.getName () ) )
                    {
                        recipientList.add ( alarmUser );
                    }
                }
                recipients = alarm.getRecipients ();
            }
        }
        catch ( Exception sqle )
        {
            sqle.printStackTrace ();
            throw new ScalarException ( "Could not load users with Board Kit Chip Access from database: ", sqle );
        }
        finally {
            if ( rset != null ) {
                try { rset.close(); } catch ( SQLException ignore ) {}
            }
            if (pstmt != null) {
                try { pstmt.close(); } catch ( SQLException ignore ) {}
            }
            if (conn != null) {
                try { conn.close(); } catch ( SQLException ignore ) {}
            }
        }
        return recipientList;
    }

    public static ArrayList loadRecipientsWithChangeQuoteAccess ( Alarm alarm ) throws ScalarException
    {
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rset = null;

        ArrayList recipientList = new ArrayList ();
        ArrayList users = new ArrayList ();

        try
        {
            conn = DataSource.getConnection ();

            StringBuffer query = new StringBuffer ();
            query.append ( SqlQueryMappings.get ( "alarms.recipient.loadRecipientsWithChangeQuoteAccess" ) );
            query.append ( "AND USER_ID IN (" );

            Iterator recipients = alarm.getRecipients ();
            // Added by Veeru on (5th July 2004) to avoid SQLException
            if(! ( recipients.hasNext() ) )
            {
                query.append("'0'");
            }
            while ( recipients.hasNext () )
            {
                AlarmUser alarmUser = ( AlarmUser ) recipients.next ();
                query.append ( "'" + alarmUser.getName () + "'" );
                if ( recipients.hasNext () )
                {
                    query.append ( ", " );
                }

            }

            query.append ( " )" );
            pstmt = conn.prepareStatement ( query.toString () );

            rset = pstmt.executeQuery ();

            while ( rset.next () )
            {
                String userId = rset.getString ( "user_id" );
                users.add ( userId );
            }

            recipients = alarm.getRecipients ();
            for ( int i = 0; i < users.size (); i++ )
            {
                String userId = ( String ) users.get ( i );
                while ( recipients.hasNext () )
                {
                    AlarmUser alarmUser = ( AlarmUser ) recipients.next ();

                    if ( userId.equals ( alarmUser.getName () ) )
                    {
                        recipientList.add ( alarmUser );
                    }
                }
                recipients = alarm.getRecipients ();
            }
        }
        catch ( Exception sqle )
        {
            sqle.printStackTrace ();
            throw new ScalarException ( "Could not load users with Board Kit Chip Access from database: ", sqle );
        }
        finally {
            if ( rset != null ) {
                try { rset.close(); } catch ( SQLException ignore ) {}
            }
            if (pstmt != null) {
                try { pstmt.close(); } catch ( SQLException ignore ) {}
            }
            if (conn != null) {
                try { conn.close(); } catch ( SQLException ignore ) {}
            }
        }
        return recipientList;
    }

}